package docker

//docker mysql 主从复制

/*

	目录
		1.启动新建主机/服务器mysql-master 3307
			1.1 docker run mysql-master
			1.2 vim my.cnf，改完后重启
			1.3 docker exec 进入容器，master容器实例内创建数据同步用户
			1.4在主数据库中查看主从同步状态  show master status;

		2.启动新建从机/服务器mysql-slave 3308
			2.1 docker run mysql-slave
			2.2 vim my.cnf，改完后重启


		4.进入mysql-slave容器内
			4.1在 从数据库中配置主从复制
			4.2在 从数据库中查看主从同步状态
			4.3在 从数据库中开启主从同步
			4.4查看从数据库状态发现已经同步

		5.主从复制测试
			5.1 主机新建库，使用库，新建表，插入数据
			5.2 从机使用库，查看记录



----------------启动主机mysql-master 3307----------------

		docker run -it --privileged=true -d  --name mysql-master -p 3307:3306 \
		-v /home/zydh/docker/lib/mysql/mysql-files:/var/lib/mysql-files/  \
		-v /home/zydh/docker/etc/mysql-master/conf:/etc/mysql \
		-v /home/zydh/docker/volume/mysql-master/data:/var/lib/mysql \
		-v /home/zydh/logs/mysql-master/log:/var/log/mysql	 \
		-e MYSQL_ROOT_PASSWORD=root \
		mysql  --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci


编辑mysql.conf
	1.进入	/home/zydh/docker/etc/mysql-master/conf	目录下新建  my.cnf
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# touch my.cnf
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# vim my.cnf


[mysqld]
##设置server_id ，同一局域网中需要唯一,主机
server_id=101
##指定不需要同步的数据库名称，mysql安装完毕后，有一个自带的mysql库，忽略
binlog-ignore-db=mysql
##开启二进制日志功能
log-bin=mall-mysql-bin
##设置二进制日志使用内存大小(事务）
binlog_cache_size=1M
##设置使用的二进制日志格式( mixed, statement, row)，mixed：混合型日志
binlog_format=mixed
##二进制日志过期清理时间。默认值为0∶表示不自动清理。设置7天清理一次
expire_logs_days=7
##跳过主从复制中遇到的所有错误或指定类型的错误﹐避免slave端复制中断。
##如∶1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

##设置使用命令跳过输入密码过程,在mysqld下添加，使用完毕后，删除
skip-grant-tables
skip-networking

		修改完容器后，需要重启
		docker restart mysql-master
		docker ps


root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker restart mysql-master
mysql-master
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker ps


		进入mysql-master主机容器

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker exec -it 5467b10bf5c2 /bin/bash
root@5467b10bf5c2:/# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@5467b10bf5c2:/#

		报错，查看docker-200*

##设置使用命令跳过输入密码过程,在mysqld下添加，使用完毕后，删除
skip-grant-tables
skip-networking


		配置完成后

root@VM-4-8-debian:~# docker exec -it mysql-master /bin/bash
root@4fd3515f74d0:/# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
......


		1.3 master容器实例内创建数据同步用户

		#添加远程登录用户并授主从复制权
		CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123456';
		GRANT replication slave,replication client ON *.* TO 'slave'@'%';



mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123456';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT replication slave,replication client ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>


		1.4在主数据库中查看主从同步状态  show master status;

	在mysql-master容器内部

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000005 |      681 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.07 sec)

mysql>



----------------启动新建从机/服务器mysql-slave 3308----------------

		2.1 docker run mysql-slave

		docker run -it --privileged=true -d  --name mysql-slave -p 3308:3306 \
		-v /home/zydh/docker/lib/mysql/mysql-files:/var/lib/mysql-files/  \
		-v /home/zydh/docker/etc/mysql-slave/conf:/etc/mysql \
		-v /home/zydh/docker/volume/mysql-slave/data:/var/lib/mysql \
		-v /home/zydh/logs/mysql-slave/log:/var/log/mysql	 \
		-e MYSQL_ROOT_PASSWORD=root \
		mysql  --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci


		2.2 vim my.cnf

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-slave/conf# pwd
/home/zydh/docker/etc/mysql-slave/conf
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-slave/conf# touch my.cnf
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-slave/conf# vim my.cnf





[mysqld]
##设置server_id 同一局域网中需要唯一
server_id=102
##指定不需要同步的数据库名称，mysql安装完毕后，有一个自带的mysql库，忽略
binlog-ignore-db=mysql
##开启二进制日志功能﹐以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin
##设置二进制日志使用内存大小(事务）
binlog_cache_size=1M
##设置使用的二进制日志格式( mixed, statement, row)，mixed：混合型日志
binlog_format=mixed
##二进制日志过期清理时间。默认值为0∶表示不自动清理。设置7天清理一次
expire_logs_days=7
##跳过主从复制中遇到的所有错误或指定类型的错误﹐避免slave端复制中断。
##如∶1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

##relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
##slave设置为只读(具有super权限的用户除外)
read_only=1


##设置使用命令跳过输入密码过程,在mysqld下添加，使用完毕后，删除
#skip-grant-tables
#skip-networking


		修改完容器后，需要重启
		docker restart mysql-slave
		docker ps




----------------进入mysql-slave容器内----------------

	docker exec -it mysql-slave /bin/bash
	mysql -uroot -proot

root@VM-4-8-debian:~# docker exec -it mysql-slave /bin/bash
root@4f372edc4939:/# mysql -uroot -proot
.....
mysql>


			4.1在 从数据库中配置主从复制

			change master to master_host='宿主机ip', master_user='slave', master_password='slave123456', master_port=3307,
			master_log_file='mall-mysql-bin.000005', master_log_pos=681, master_connect_retry=30;

			master_host=宿主机ip							主数据库的IP地址;/当前宿主机ip
			master_port=3307  							主数据库的运行端口;/主机的端口
			master_user='slave' 						在主数据库创建的用于同步数据的用户账号;/在master里的用户  上面master创建的授权用户
			master_password='slave123456'  				在主数据库创建的用于同步数据的用户密码;/master用户slave的密码  上面master创建的授权用户
			master_log_file='mall-mysql-bin.000005' 	指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数/上面查询 show master status; 得出的结果
			master_log_pos=681 						 	指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数/上面查询 show master status; 得出的结果
			master_connect_retry=30						连接失败重试的时间间隔，单位为秒。



			change master to master_host='【宿主机ip】', master_user='slave', master_password='slave123456', master_port=3307,
			master_log_file='mall-mysql-bin.000005', master_log_pos=681, master_connect_retry=30;



mysql> change master to master_host='【宿主机ip】', master_user='slave', master_password='slave123456', master_port=3307,
    -> master_log_file='mall-mysql-bin.000005', master_log_pos=681, master_connect_retry=30;
Query OK, 0 rows affected, 10 warnings (0.28 sec)


			4.2在 从数据库中查看主从同步状态
					show slave status;
					or
					show slave status \G;
							## \G 已键值对的方式

					....
				    Slave_IO_Running: No
            		Slave_SQL_Running: No
					....
					说明从机还没有开始主从复制


mysql> show slave status;
+----------------+----------------+-------------+-------------+---------------+-----------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File       | Read_Master_Log_Pos | Relay_Log_File              | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------+----------------+-------------+-------------+---------------+-----------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
|                | 【宿主机ip】 | slave       |        3307 |            30 | mall-mysql-bin.000005 |                 681 | mall-mysql-relay-bin.000001 |             4 | mall-mysql-bin.000005 | No               | No                |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 681 |             156 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL |                         |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
+----------------+----------------+-------------+-------------+---------------+-----------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.04 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 【宿主机ip】
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000005
          Read_Master_Log_Pos: 681
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No
			.......
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql>




			4.3在 从数据库中开启主从同步

			  start slave;


mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.19 sec)

mysql>




			4.4查看从数据库状态发现已经同步

				show slave status \G;

				.....
				Slave_IO_Running: Connecting   ##状态由 no 变为了 Connecting，并没有变为yes，说明有问题
            	Slave_SQL_Running: Yes         ##状态由 no 变为了 yes
				...
 				Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@【宿主机ip】:3307' - retry-time: 30 retries: 6 message: Can't connect to MySQL server on '【宿主机ip】:3307' (110)
					#错误，链接不到 ip:3307 ，发现自己并没有开放对应的端口，所以链接不到，在阿里云/腾讯云 安全组开放3307,3308的端口，如果自身有防火墙，防火墙同样要开放。


mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 【宿主机ip】
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000005
          Read_Master_Log_Pos: 681
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
				....
        Seconds_Behind_Master: NULL
		Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@【宿主机ip】:3307' - retry-time: 30 retries: 6 message: Can't connect to MySQL server on '【宿主机ip】:3307' (110)

mysql>

			在阿里云/腾讯云的  安全组/防火墙 开放端口后再查询
			....
	        Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
			.....



----------------主从复制测试----------------


			5.1 主机新建库，使用库，新建表，插入数据

mysql> create database db01;
Query OK, 1 row affected (0.13 sec)

mysql> use db01;
Database changed
mysql> create table t1 (id int,name varchar(25));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t1 values(1,"ok");
Query OK, 1 row affected (0.18 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | ok   |
+------+------+
1 row in set (0.01 sec)

mysql>




			5.2 从机使用库，查看记录

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.14 sec)

mysql>


mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | ok   |
+------+------+
1 row in set (0.06 sec)

mysql>


*/
